今天介紹 Archive 儲存引擎.顧名思義就是將資料存檔.特色就是輸入
快,適合用來放 log 資料,可以壓縮資料.只能 INSERT 跟 SELECT ,
不能 UPDATE 與 DELETE , 很適合 log 資料,不怕被誤修改或誤刪除.
也不能放 BLOB等新的資料型態,以傳統資料型態為主.

-- 使用系統字典檔來當測試資料, 登入時加上 --local_infile
-- 建立 Table
CREATE TABLE archive1(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
data CHAR(100) NOT NULL) ENGINE=ARCHIVE;

-- 載入資料
LOAD DATA INFILE '/usr/share/dict/words' INTO TABLE archive1(data);

-- 察看空間使用情形並與 InnoDB / MyISAM 等比較
SELECT table_name, table_rows, row_format,
        data_length / 1024 AS 'data(K)',
        index_length / 1024 AS 'index(K)'
FROM INFORMATION_SCHEMA.TABLES
WHERE table_name IN ( 'innodb2', 'myisam1', 'archive1');

+------------+------------+------------+------------+----------+
| table_name | table_rows | row_format | data(K)    | index(K) |
+------------+------------+------------+------------+----------+
| archive1   |      99171 | Compressed |   616.4736 |   0.0000 |
| innodb2    |      99270 | Compressed |  6920.0000 |   0.0000 |
| myisam1    |      99171 | Fixed      | 29538.2373 | 996.0000 |
+------------+------------+------------+------------+----------+

-- 檔案系統裡實際佔 631269 bytes, 約合 616.47 K
-rw-rw---- 1 mysql mysql   631269 10月  9 16:53 archive1.ARZ
-rw-rw---- 1 mysql mysql     8586 10月  9 16:51 archive1.frm

接著試試看能否刪除或更新資料,或是 ALTER TABLE.

DELETE FROM archive1;
回報錯誤訊息:ERROR 1031 (HY000): Table storage engine for 'archive1' doesn't have this option

TRUNCATE TABLE archive1;
回報錯誤訊息:ERROR 1031 (HY000): Table storage engine for 'archive1' doesn't have this option

UPDATE archive1 SET data = 'Bad Update' WHERE id = 1;
回報錯誤訊息:ERROR 1031 (HY000): Table storage engine for 'archive1' doesn't have this option

ALTER TABLE archive1 DROP COLUMN data;
回報錯誤訊息:ERROR 1022 (23000): Can't write; duplicate key in table '#sql-41b_3f'

-- 察看是否被改變,資料數目是否相符
DESC archive1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| data  | char(100)        | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

SELECT COUNT(1) FROM archive1;
+----------+
| COUNT(1) |
+----------+
|    99171 |
+----------+

能夠有效保護資料,很適合用來存放紀錄.
MySQL 提供許多特殊的儲存引擎,我們可以視情況靈活應用.